<?php
	header('content-type:text/html;charset=utf-8');
	
	$db21 = array(); //3.21的服务器结果集
	$fwq = array(); //点胶服务器的结果集
	
	$dbName = "sqlsrv:Server=172.31.20.106,1433;Database=DMS";
	$dbUser = "sa";
	$dbPassword = "password01!";
	
	//点胶结果集函数
	function fruit($ID, $GDH, $INVCODE, $ZL) {
		global $fwq;
		$fwq[$ID] = array($GDH, $INVCODE, $ZL);
		// print_r("<br>");
		// print_r($ID);
		// print_r("<br>");
		// print_r(json_encode($fwq[$ID]));
	}
	
	//321结果集函数
	function db321($ID, $GDH, $INVCODE, $ZL) {
		global $db21;
		$db21[$ID] = array($GDH, $INVCODE, $ZL);
		// print_r("<br>");
		// print_r($ID);
		// print_r("<br>");
		// print_r(json_encode($db21[$ID]));
	}
	
	try {
		$my21 = new PDO("mysql:host=172.31.3.21;port=3306;dbname=tddb;","root","tdled2018", array(PDO::ATTR_PERSISTENT => true));
		$my21->query("set character set 'utf8'");//读库
		$my21->query("set names 'utf8'");//写库
		
		$SQL1 = "
		SELECT
			 `ID` AS 'ID'
			, `工单号` AS 'GDH'
			, `子件料号` AS 'INVCODE'
			, `实际重量` AS 'ZL'
		FROM
			`jiaoshuidata`
		";
		$res=$my21->query($SQL1);//准备查询语句
		$res->fetchAll(PDO::FETCH_FUNC, "db321");
		
		$dj1 = new PDO($dbName, $dbUser, $dbPassword, array(PDO::ATTR_PERSISTENT => true));
		
		$sql = "
		-- 服务器1新语句 验证OK,无重复值
		SELECT
			 'fwq1|' + LEFT(OH.OrderNo,11) + '|' + LEFT(OOD.MaterialNo,7) AS 'ID'
			,LEFT(OH.OrderNo,11) AS 'GDH'
			,LEFT(OOD.MaterialNo,7) AS 'INVCODE'
			,SUM(OOD.ActuralNum) AS 'ZL'
		FROM
			[DMS].[dbo].[T_Order] AS OH --订单头
		INNER JOIN
			[DMS].[dbo].[T_OrderOper] AS OO --订单配胶头
		ON
			OH.ID = OO.OrderID
		INNER JOIN
			[DMS].[dbo].[T_OrderOperDetail] AS OOD --订单配胶明细
		ON
			OO.ID = OOD.OrderOperID
		WHERE
			OOD.EndTime IS NOT NULL
		AND
			OO.Status = 'OK'
		GROUP BY
			 LEFT(OH.OrderNo,11)
			,LEFT(OOD.MaterialNo,7)
		;";
		
		$res = $dj1->query($sql);
		$res->fetchAll(PDO::FETCH_FUNC, "fruit");
		
		$dbName = "sqlsrv:Server=172.31.20.164,1433;Database=DMS3";
		$dj2 = new PDO($dbName, $dbUser, $dbPassword, array(PDO::ATTR_PERSISTENT => true));
		
		$sql = "
		-- 服务器2新语句
		SELECT
			'fwq2|' + LEFT(OH.OrderNo,11) + '|' + LEFT(OOD.MaterialNo,7) AS 'ID'
			,LEFT(OH.OrderNo,11) AS 'GDH'
			,LEFT(OOD.MaterialNo,7) AS 'INVCODE'
			,SUM(OOD.ActuralNum) AS 'ZL'
		FROM
			[DMS3].[dbo].[T_Order] AS OH --订单头
		INNER JOIN
			[DMS3].[dbo].[T_OrderOper] AS OO --订单配胶头
		ON
			OH.ID = OO.OrderID
		INNER JOIN
			[DMS3].[dbo].[T_OrderOperDetail] AS OOD --订单配胶明细
		ON
			OO.ID = OOD.OrderOperID
		WHERE
			OOD.EndTime IS NOT NULL
		AND
			OO.Status = 'OK'
		GROUP BY
			LEFT(OH.OrderNo,11)
			,LEFT(OOD.MaterialNo,7)
		;";
		
		$re2 = $dj2->query($sql);
		$re2->fetchAll(PDO::FETCH_FUNC, "fruit");
		
		echo "The connection is successful!<br/>";
	} catch (PDOException $e) {
		die ("Error!: " . $e->getMessage() . "<br/>");
	}
	
	//对比2
	function myfunction($a,$b)
	{
		$arr1 = array();
		$upd = array();
		foreach($a as $k => $v)
		{
			if(array_key_exists($k,$b))
			{
				if($a[$k][2] == $b[$k][2])
				{
					
				}
				else
				{
					$upd[$k] = $a[$k];
				}
			}
			else
			{
				$arr1[$k] = $a[$k];
			}
		}
		return array('0' => $arr1, '1' => $upd);
	}
	
	$result = myfunction($fwq,$db21);
	// print_r(json_encode($result));
	$HSQL = "INSERT INTO `jiaoshuidata`(`ID`, `工单号`, `子件料号`, `实际重量`) VALUES ";
	$DSQL = "";
	foreach($result['0'] as $k => $v)
	{
		if($DSQL != "")
		{
			$DSQL .= ",('" . $k ."','" . $v[0] ."','" . $v[1] ."','" . $v[2] ."')";
		}
		else
		{
			$DSQL = " ('" . $k ."','" . $v[0] ."','" . $v[1] ."','" . $v[2] ."')";
		}
	}
	$res=$my21->query($HSQL . $DSQL . ";");//准备查询语句
	var_dump($res);
	
	$SQL = "";
	foreach($result['1'] as $k => $v)
	{
		$SQL .= "UPDATE `jiaoshuidata` SET `工单号`='" . $v[0] . "',`子件料号`='" . $v[1] . "',`实际重量`='" . $v[2] . "' WHERE `ID`='" . $k . "';";
	}
	$res=$my21->query($SQL);//准备查询语句
	print_r("<br>----------<br>");
	var_dump($res);
?>